<?php
	
	require_once(ROOTDIR.'source/service/class/class.main.php');
	
	class _SqlTool extends Main
	{
		public static function getDistinctCount($table,$wheres,$distinct,$between)
		{
			$where = ' where 1=1 ';
			if(is_array($wheres) && $wheres)
			{
				foreach($wheres as $k => $v)
				{
					if(is_array($v))
					{
						$like = $v[0];
						
						if($like == 'like')
						{
							$where.=" and `{$k}` {$like} '%".self::getMysqlInstance()->strescape($v[1])."%'";
						}
						elseif($like == 'in' || $like == 'not in')
						{
							$where.=" and `{$k}` {$like} (".$v[1].")";
						}
						elseif($like == 'is')
						{
							$where.=" and `{$k}` {$like} ".$v[1];
						}
						else
						{
							$where.=" and `{$k}` {$like} '".self::getMysqlInstance()->strescape($v[1])."'";
						}
						
					}
					else
					{
						$where.=" and `{$k}`='".self::getMysqlInstance()->strescape($v)."'";
					}
					
					
				}
			}
			
			if(isset($between['start']) && isset($between['end']))
			{
				$where .= ' and `addtime` between "'.$between['start'].'" and "'.$between['end'].'" ';
			}
			
			$sql = sprintf("select count(1) as count from (select DISTINCT %s from %s %s ) as t ",$distinct,$table,$where);
			
			fileLog($sql,'debug');
			
			$resCount = self::getMysqlInstance()->query($sql);
			
			return intval($resCount[0]['count']);
		}
		
		
		public static function getDistinctList($table,$wheres,$distinct,$between)
		{
			$where = ' where 1=1 ';
			if(is_array($wheres) && $wheres)
			{
				foreach($wheres as $k => $v)
				{
					if(is_array($v))
					{
						$like = $v[0];
						
						if($like == 'like')
						{
							$where.=" and `{$k}` {$like} '%".self::getMysqlInstance()->strescape($v[1])."%'";
						}
						elseif($like == 'in' || $like == 'not in')
						{
							$where.=" and `{$k}` {$like} (".$v[1].")";
						}
						elseif($like == 'is')
						{
							$where.=" and `{$k}` {$like} ".$v[1];
						}
						else
						{
							$where.=" and `{$k}` {$like} '".self::getMysqlInstance()->strescape($v[1])."'";
						}
						
					}
					else
					{
						$where.=" and `{$k}`='".self::getMysqlInstance()->strescape($v)."'";
					}
					
					
				}
			}
			
			if(isset($between['start']) && isset($between['end']))
			{
				if(!empty($between['ziduan']))
				{
					$where .= ' and `'.$between['ziduan'].'` between "'.$between['start'].'" and "'.$between['end'].'" ';
				}
				else
				{
					$where .= ' and `addtime` between "'.$between['start'].'" and "'.$between['end'].'" ';
				}
				
			}
			
			$sql = sprintf("select DISTINCT %s from %s %s ",$distinct,$table,$where);
			
			fileLog($sql,'debug');
			
			if(!empty($pages))
			{
				$result['list'] = self::getMysqlInstance()->query($sql);
				
				$result['pages'] = $pagenum;
			}
			else
			{
				$result['list'] =  self::getMysqlInstance()->query($sql);
			}
			
			return $result;
		}
		
		
		public static function getZijinList($wheres,$pages)
		{
			$time = $wheres['time'];
			unset($wheres['time']);
			$where = '';
			
			foreach($wheres as $k => $v)
			{
				$where.=" and `{$k}`='".self::getMysqlInstance()->strescape($v)."'";
			}
			
			if(!empty($pages))
			{
				$sqlCount = sprintf("select count(1) as count from (select t1.*,(select SUM(money) from jyinfo t2 where t1.id=t2.userid and t2.addtime < %s ) as sum from users t1 where 1=1 %s ) as a where sum > 0",$time,$where);
			
				fileLog($sqlCount,'debug');
			
				$resCount = self::getMysqlInstance()->query($sqlCount);
				
				if(!empty($resCount[0]))
				{
					$pagenum=ceil($resCount[0]['count']/$pages['rows']); 
				}
				else
				{
					$pagenum=ceil(0/$pages['rows']); 
				}
				
				$offset=($pages['page']-1)*$pages['rows'];   
				$limit = " limit $offset,".$pages['rows'];
			}
				
			$order =' order by regtime desc ';
			
			$sql = sprintf("select * from (select t1.*,(select SUM(money) from jyinfo t2 where t1.id=t2.userid and t2.addtime < %s ) as sum from users t1 where 1=1 %s ) as a where sum >0 %s %s ",$time,$where,$order,$limit);
			
			fileLog($sql,'debug');
			
			if(!empty($pages))
			{
				$result['list'] = self::getMysqlInstance()->query($sql);
				
				$result['pages'] = $pagenum;
			}
			else
			{
				$result['list'] =  self::getMysqlInstance()->query($sql);
			}
			
			return $result;
		}
		
		public static function addInfo($table,$data)
		{
			$set = join(',',self::setMySQLParam($data));
			if(!empty($set))
			{
				$sql = sprintf("insert into %s set %s ",$table,$set);
				fileLog($sql);
				return self::getMysqlInstance()->insert_sql($sql);
			}
			else
			{
				return false;
			}
		}
		
		public static function tixian($wheres,$tixian)
		{
			$link = self::getMysqlInstance();
			$link->query("SET AUTOCOMMIT=0");
			$link->query("BEGIN");
			$resadd = self::addInfo('jyinfo', $tixian);
			if($resadd)
			{
				$yue = self::getSum('jyinfo', $wheres, $between, 'money');
				if($yue>=0)
				{
					$link->query("COMMIT");
					return true;
				}
				else
				{
					$link->query("ROLLBACK");
				}
			}
			else
			{
				$link->query("ROLLBACK");
			}
			
			$link->query("END");
			$link->query("SET AUTOCOMMIT=1");
			
			return false;
		}
		
		
		
		public static function autoTixian($tixian,$fanli)
		{
			$link = self::getMysqlInstance();
			$link->query("SET AUTOCOMMIT=0");
			$link->query("BEGIN");
			$resadd1 = self::addInfo('txinfo', $tixian);
			
			if($resadd1)
			{
				$resadd2 = self::addInfo('jyinfo', $fanli);
				if($resadd2)
				{
					$link->query("COMMIT");
				}
				else
				{
					$link->query("ROLLBACK");
				}
			}
			else
			{
				$link->query("ROLLBACK");
			}
			
			$link->query("END");
			$link->query("SET AUTOCOMMIT=1");
			
			return $resadd1 && $resadd2;
		}
		
		private static function chuliYaoqing($tzinfo,$puser)
		{
			$resList = self::getList('yqguize');
			unset($list);
			foreach ($resList as $key => $value)
			{
				$list[$value['type']] = $value;
			}
			
			if(!empty($list['1']))
			{
				unset($where);
				$where['p_userid'] = $puser['id'];
				$where['type'] = '1';
				$info1 = self::getInfo('wancheng', $where);
				if($tzinfo['money'] >= $list['1']['touzi'] && empty($info1))
				{
					unset($data);
					$data['userid'] = $tzinfo['userid'];
					$data['p_userid'] = $puser['id'];
					$data['type'] = '1';
					$data['addtime'] = time();
					$resadd = self::addInfo('wancheng', $data);
					
					if($resadd)
					{
						$money = $list['1']['jiangli'];
						if($money > 0)
						{
							unset($data);
							$data['userid'] = $tzinfo['userid'];
							$data['p_userid'] = $puser['id'];
							$data['type'] = '1';
							$data['touzi'] = $tzinfo['money'];
							$data['money'] = $money;
							$data['desc'] = '邀请收益';
							$resadd = self::addInfo('yaoqing', $data);
							if($resadd)
							{
								unset($data);
								$data['userid'] = $puser['id'];
								$data['money'] = $money;
								$data['title'] = '邀请收益';
								$data['addtime'] = $tzinfo['fltime'];
								
								$resadd = self::addInfo('jyinfo', $data);
								if($resadd)
								{
									$bool1 = true;
								}
								else
								{
									$bool1 = false;
								}
							}
							else
							{
								$bool1 = false;
							}
						}
						else
						{
							$bool1 = false;
						}
						
					}
					else
					{
						$bool1 = false;
					}
					
				}
				else
				{
					$bool1 = true;
				}
			}
			else
			{
				$bool1 = true;
			}
			
			if(!empty($list['2']))
			{
				if($tzinfo['money'] >= $list['2']['touzi'])
				{
					unset($data);
					$data['userid'] = $tzinfo['userid'];
					$data['p_userid'] = $puser['id'];
					$data['type'] = '2';
					$data['addtime'] = time();
					$resadd = self::addInfo('wancheng', $data);
					
					if($resadd)
					{
						unset($where);
						$where['p_userid'] = $puser['id'];
						$where['type'] = '2';
						$count = self::getCount('wancheng', $where);
						if($count == 5)
						{
							$money = $list['2']['jiangli'];
							if($money > 0)
							{
								unset($data);
								$data['userid'] = $tzinfo['userid'];
								$data['p_userid'] = $puser['id'];
								$data['type'] = '2';
								$data['touzi'] = $tzinfo['money'];
								$data['money'] = $money;
								$data['desc'] = '邀请收益';
								$resadd = self::addInfo('yaoqing', $data);
								if($resadd)
								{
									unset($data);
									$data['userid'] = $puser['id'];
									$data['money'] = $money;
									$data['title'] = '邀请收益';
									$data['addtime'] = $tzinfo['fltime'];
									
									$resadd = self::addInfo('jyinfo', $data);
									if($resadd)
									{
										$bool2 = true;
									}
									else
									{
										$bool2 = false;
									}
								}
								else
								{
									$bool2 = false;
								}
							}
							else
							{
								$bool2 = false;
							}
						}
						else
						{
							$bool2 = true;
						}
					}
					else
					{
						$bool2 = false;
					}
					
				}
				else
				{
					$bool2 = true;
				}
			}
			else
			{
				$bool2 = true;
			}
			
			if(!empty($list['3']))
			{
				$money = $tzinfo['money'] * ($list['3']['jiangli'] / 100);
				
				if($money > 0)
				{
					unset($data);
					$data['userid'] = $tzinfo['userid'];
					$data['p_userid'] = $puser['id'];
					$data['type'] = '3';
					$data['money'] = $money;
					$data['touzi'] = $tzinfo['money'];
					$data['desc'] = '邀请收益';
					$resadd = self::addInfo('yaoqing', $data);
					if($resadd)
					{
						unset($data);
						$data['userid'] = $puser['id'];
						$data['money'] = $money;
						$data['title'] = '邀请收益';
						$data['addtime'] = $tzinfo['fltime'];
						
						$resadd = self::addInfo('jyinfo', $data);
						if($resadd)
						{
							$bool3 = true;
						}
						else
						{
							$bool3 = false;
						}
					}
					else
					{
						$bool3 = false;
					}
				}
				else
				{
					$bool3 = false;
				}
			}
			else
			{
				$bool3 = true;
			}
			
			
			return $bool1 && $bool2 && $bool3;
		}
		
		public static function autoFanli($where,$data,$fanli,$puser,$touzi)
		{
			$link = self::getMysqlInstance();
			$link->query("SET AUTOCOMMIT=0");
			$link->query("BEGIN");
			$resup = self::setInfo('tzinfo', $where, $data);
			
			if($resup)
			{
				$resadd = self::addInfo('jyinfo', $fanli);
				if($resadd)
				{
					if(!empty($puser))
					{
						$resadd2 = self::chuliYaoqing($touzi,$puser);
						if($resadd2)
						{
							$link->query("COMMIT");
						}
						else
						{
							$link->query("ROLLBACK");
						}
					}
					else
					{
						$resadd2 = true;
						$link->query("COMMIT");
					}
				}
				else
				{
					$link->query("ROLLBACK");
				}
			}
			else
			{
				$link->query("ROLLBACK");
			}
			
			$link->query("END");
			$link->query("SET AUTOCOMMIT=1");
			
			return $resup && $resadd && $resadd2;
		}
		
		public static function setInfo($table,$where,$data)
		{
			$set = join(',',self::setMySQLParam($data));
			if(!empty($set) && !empty($where['id']))
			{
				$sql = sprintf("update %s set %s where id='%s'",$table,$set,$where['id']);
				fileLog($sql);
				return self::getMysqlInstance()->diy_update_sql($sql);
			}
			else
			{
				return false;
			}
		}
		
		public static function getInfo($table,$wheres)
		{
			$where = join(' and ',self::setMySQLParam($wheres));
			$sql = sprintf("select * from %s where %s",$table,$where);
			fileLog($sql);
			$res = self::getMysqlInstance()->query($sql);
			
			return $res[0];
		}

		public static function getOplogList($table,$wheres,$page,$sort,$between)
		{
			$where =' where 1=1';
			
			if(is_array($wheres) && $wheres)
			{
				foreach($wheres as $k => $v)
				{
					if(is_array($v))
					{
						$like = $v[0];
						
						if($like == 'like')
						{
							$where.=" and `{$k}` {$like} '%".self::getMysqlInstance()->strescape($v[1])."%'";
						}
						elseif($like == 'in' || $like == 'not in')
						{
							$where.=" and `{$k}` {$like} (".$v[1].")";
						}
						elseif($like == 'is')
						{
							$where.=" and `{$k}` {$like} ".$v[1];
						}
						else
						{
							$where.=" and `{$k}` {$like} '".self::getMysqlInstance()->strescape($v[1])."'";
						}
						
					}
					else
					{
						$where.=" and `{$k}`='".self::getMysqlInstance()->strescape($v)."'";
					}
					
					
				}
			}
			
			if(!empty($page))
			{
				$sqlCount = "select count(1) as count from {$table} ".$where;
			
				fileLog($sqlCount,'debug');
			
				$resCount = self::getMysqlInstance()->query($sqlCount);
				
				if(!empty($resCount[0]))
				{
					$pagenum=ceil($resCount[0]['count']/$page['rows']); 
				}
				else
				{
					$pagenum=ceil(0/$page['rows']); 
				}
				
				$offset=($page['page']-1)*$page['rows'];   
				$limit = " limit $offset,".$page['rows'];
			}
			$select = 'select * ';
			
			$order =' order by b.optime desc ';
	
			$sql = sprintf("%s from %s as a,(select MAX(optime) as optime,oplog.union_id from oplog  where `table`='%s' GROUP BY union_id) as b %s and a.id = b.union_id  %s %s",$select,$table,$table,$where,$order,$limit);
			
			fileLog($sql,'debug');
			
			if(!empty($page))
			{
				$result['list'] = self::getMysqlInstance()->query($sql);
				
				$result['pages'] = $pagenum;
			}
			else
			{
				$result =  self::getMysqlInstance()->query($sql);
			}
			return $result;
		}
		
		public static function getList($table,$wheres,$page,$sort,$between,$filed='addtime')
		{
			$where =' 1=1 ';
			
			if(is_array($wheres) && $wheres)
			{
				foreach($wheres as $k => $v)
				{
					if(is_array($v))
					{
						$like = $v[0];
						
						if($like == 'like')
						{
							$where.=" and `{$k}` {$like} '%".self::getMysqlInstance()->strescape($v[1])."%'";
						}
						elseif($like == 'in' || $like == 'not in')
						{
							$where.=" and `{$k}` {$like} (".$v[1].")";
						}
						elseif($like == 'is')
						{
							$where.=" and `{$k}` {$like} ".$v[1];
						}
						else
						{
							$where.=" and `{$k}` {$like} '".self::getMysqlInstance()->strescape($v[1])."'";
						}
						
					}
					else
					{
						$where.=" and `{$k}`='".self::getMysqlInstance()->strescape($v)."'";
					}
					
					
				}
			}
			
			if(isset($between['start']) && isset($between['end']))
			{
				$where .= ' and `'.$filed.'` between "'.$between['start'].'" and "'.$between['end'].'" ';
			}
			
			if(!empty($page))
			{
				$sqlCount = "select count(1) as count from {$table} where ".$where;
			
				fileLog($sqlCount,'debug');
			
				$resCount = self::getMysqlInstance()->query($sqlCount);
				
				if(!empty($resCount[0]))
				{
					$pagenum=ceil($resCount[0]['count']/$page['rows']); 
				}
				else
				{
					$pagenum=ceil(0/$page['rows']); 
				}
				
				$offset=($page['page']-1)*$page['rows'];   
				$limit = " limit $offset,".$page['rows'];
			}
			$select = 'select * ';
			if(is_array($sort) && $sort)
			{
				$order =' order by ';
				foreach($sort as $k => $v)
				{
					$ordertemp[] =" `{$k}` ".self::getMysqlInstance()->strescape($v);
				}
				
				$order .= implode(',', $ordertemp);
			}
			$sql = sprintf("%s from %s where %s %s %s",$select,$table,$where,$order,$limit);
			fileLog($sql);
			if(!empty($page))
			{
				$result['list'] = self::getMysqlInstance()->query($sql);
				
				$result['pages'] = $pagenum;
			}
			else
			{
				$result =  self::getMysqlInstance()->query($sql);
			}
			return $result;
		}
		
		public static function getCount($table,$wheres,$between)
		{
			$where =' 1=1 ';
			
			if(is_array($wheres) && $wheres)
			{
				foreach($wheres as $k => $v)
				{
					if(is_array($v))
					{
						$like = $v[0];
						
						if($like == 'like')
						{
							$where.=" and `{$k}` {$like} '%".self::getMysqlInstance()->strescape($v[1])."%'";
						}
						elseif($like == 'in' || $like == 'not in')
						{
							$where.=" and `{$k}` {$like} (".$v[1].")";
						}
						elseif($like == 'is')
						{
							$where.=" and `{$k}` {$like} ".$v[1];
						}
						else
						{
							$where.=" and `{$k}` {$like} '".self::getMysqlInstance()->strescape($v[1])."'";
						}
						
					}
					else
					{
						$where.=" and `{$k}`='".self::getMysqlInstance()->strescape($v)."'";
					}
				}
			}
			
			if(isset($between['start']) && isset($between['end']))
			{
				$where .= ' and `addtime` between "'.$between['start'].'" and "'.$between['end'].'" ';
			}
			
		
			$sqlCount = "select count(1) as count from {$table} where ".$where;
			
			fileLog($sqlCount,'debug');
			
			$resCount = self::getMysqlInstance()->query($sqlCount);
			
			return intval($resCount[0]['count']);
		}
		
		public static function getSum($table,$wheres,$between,$sum)
		{
			$where =' 1=1 ';
			
			if(is_array($wheres) && $wheres)
			{
				foreach($wheres as $k => $v)
				{
					if(is_array($v))
					{
						$like = $v[0];
						
						if($like == 'like')
						{
							$where.=" and `{$k}` {$like} '%".self::getMysqlInstance()->strescape($v[1])."%'";
						}
						elseif($like == 'in' || $like == 'not in')
						{
							$where.=" and `{$k}` {$like} (".$v[1].")";
						}
						elseif($like == 'is')
						{
							$where.=" and `{$k}` {$like} ".$v[1];
						}
						else
						{
							$where.=" and `{$k}` {$like} '".self::getMysqlInstance()->strescape($v[1])."'";
						}
						
					}
					else
					{
						$where.=" and `{$k}`='".self::getMysqlInstance()->strescape($v)."'";
					}
					
					
				}
			}
			
			if(isset($between['start']) && isset($between['end']))
			{
				$where .= ' and `addtime` between "'.$between['start'].'" and "'.$between['end'].'" ';
			}
			
		
			$sqlCount = "select sum($sum) as sum from {$table} where ".$where;
			
			fileLog($sqlCount,'debug');
			
			$resCount = self::getMysqlInstance()->query($sqlCount);
			
			if(!empty($resCount[0]['sum']))
			{
				return $resCount[0]['sum'];
			}
			else
			{
				return '0.00';
			}
		}

		public static function getMax($table,$wheres,$between,$filed)
		{
			$where =' 1=1 ';
			
			if(is_array($wheres) && $wheres)
			{
				foreach($wheres as $k => $v)
				{
					if(is_array($v))
					{
						$like = $v[0];
						
						if($like == 'like')
						{
							$where.=" and `{$k}` {$like} '%".self::getMysqlInstance()->strescape($v[1])."%'";
						}
						elseif($like == 'in' || $like == 'not in')
						{
							$where.=" and `{$k}` {$like} (".$v[1].")";
						}
						elseif($like == 'is')
						{
							$where.=" and `{$k}` {$like} ".$v[1];
						}
						else
						{
							$where.=" and `{$k}` {$like} '".self::getMysqlInstance()->strescape($v[1])."'";
						}
						
					}
					else
					{
						$where.=" and `{$k}`='".self::getMysqlInstance()->strescape($v)."'";
					}
				}
			}
			
			if(isset($between['start']) && isset($between['end']))
			{
				$where .= ' and `addtime` between "'.$between['start'].'" and "'.$between['end'].'" ';
			}
			
		
			$sqlCount = "select max($filed) as max from {$table} where ".$where;
			
			fileLog($sqlCount,'debug');
			
			$resCount = self::getMysqlInstance()->query($sqlCount);
			
			return $resCount[0]['max'];
		}

		public static function getMin($table,$wheres,$between,$filed)
		{
			$where =' 1=1 ';
			
			if(is_array($wheres) && $wheres)
			{
				foreach($wheres as $k => $v)
				{
					if(is_array($v))
					{
						$like = $v[0];
						
						if($like == 'like')
						{
							$where.=" and `{$k}` {$like} '%".self::getMysqlInstance()->strescape($v[1])."%'";
						}
						elseif($like == 'in' || $like == 'not in')
						{
							$where.=" and `{$k}` {$like} (".$v[1].")";
						}
						elseif($like == 'is')
						{
							$where.=" and `{$k}` {$like} ".$v[1];
						}
						else
						{
							$where.=" and `{$k}` {$like} '".self::getMysqlInstance()->strescape($v[1])."'";
						}
						
					}
					else
					{
						$where.=" and `{$k}`='".self::getMysqlInstance()->strescape($v)."'";
					}
				}
			}
			
			if(isset($between['start']) && isset($between['end']))
			{
				$where .= ' and `addtime` between "'.$between['start'].'" and "'.$between['end'].'" ';
			}
			
		
			$sqlCount = "select min($filed) as min from {$table} where ".$where;
			
			fileLog($sqlCount,'debug');
			
			$resCount = self::getMysqlInstance()->query($sqlCount);
			
			return $resCount[0]['min'];
		}

		public static function getMinByFileds($table,$wheres,$between,$filed,$fileds='id')
		{
			$where =' 1=1 ';
			
			if(is_array($wheres) && $wheres)
			{
				foreach($wheres as $k => $v)
				{
					if(is_array($v))
					{
						$like = $v[0];
						
						if($like == 'like')
						{
							$where.=" and `{$k}` {$like} '%".self::getMysqlInstance()->strescape($v[1])."%'";
						}
						elseif($like == 'in' || $like == 'not in')
						{
							$where.=" and `{$k}` {$like} (".$v[1].")";
						}
						elseif($like == 'is')
						{
							$where.=" and `{$k}` {$like} ".$v[1];
						}
						else
						{
							$where.=" and `{$k}` {$like} '".self::getMysqlInstance()->strescape($v[1])."'";
						}
						
					}
					else
					{
						$where.=" and `{$k}`='".self::getMysqlInstance()->strescape($v)."'";
					}
				}
			}
			
			if(isset($between['start']) && isset($between['end']))
			{
				$where .= ' and `addtime` between "'.$between['start'].'" and "'.$between['end'].'" ';
			}
			
		
			$sqlCount = "select min($filed) as min,$fileds from {$table} where ".$where;
			
			fileLog($sqlCount,'debug');
			
			$resCount = self::getMysqlInstance()->query($sqlCount);
			
			return $resCount[0];
		}


		public static function removeInfo($table,$wheres)
		{
			$where = join(',',self::setMySQLParam($wheres));
			if(!empty($where))
			{
				$sql = sprintf("delete from %s where %s",$table,$where);
				fileLog($sql);
				return self::getMysqlInstance()->diy_update_sql($sql);
			}
			else
			{
				return false;
			}
			
			
		}
	}